package com.pentofab.payrollmanagement.database.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedHashMap;

import com.pentofab.payrollmanagement.dao.model.Attendance;
import com.pentofab.payrollmanagement.dao.model.Employee;
import com.pentofab.payrollmanagement.database.AttendanceRepository;
import com.pentofab.payrollmanagement.exception.BusinessException;
import com.pentofab.payrollmanagement.exception.ExceptionMsg;
import com.pentofab.payrollmanagement.utilities.CustomTableConstants;
import com.pentofab.payrollmanagement.utilities.PayRollUtility;

// TODO: Auto-generated Javadoc
/**
 * The Class PayRollRepositoryImpl.
 */
public class AttendanceRepositoryImpl implements AttendanceRepository {

	/** The conn. */
	
	private Connection conn = null;

	public AttendanceRepositoryImpl() {
		conn = CreateDBConnection.cteateConnectionObj();

	}
	public void addAttandeanceEntry(String empId, String date,
			String loginTime, String logOutTime, String createdEmp, int rowNum, boolean firstIn,boolean lastOut)
			throws Exception {
		PreparedStatement preparedStatement = conn
				.prepareStatement(AttendanceTableSQLConstants.inserIntoAttandance);
		// Emp ID
		preparedStatement.setString(1, empId);
		// Date
		preparedStatement.setString(2, date);
		// Login time
		preparedStatement.setString(3, loginTime);
		// Log out time
		preparedStatement.setString(4, logOutTime);
		// Created by
		preparedStatement.setString(5, createdEmp);
		// Modified emp is not required to insert row
		preparedStatement.setString(6, "");
		// Comments not rqquired to enter a row
		preparedStatement.setString(7, "");
		// ROw number is necessary
		preparedStatement.setInt(8, rowNum);
		preparedStatement.setBoolean(9, firstIn);
		preparedStatement.setBoolean(10, lastOut);
		
		preparedStatement.setString(11, loginTime);
		preparedStatement.setString(12, logOutTime);

		PayRollUtility.debug(preparedStatement.toString());
		System.out.println("preparedStatement : "+preparedStatement);
		int status = preparedStatement.executeUpdate();

		PayRollUtility.debug("addAttandeanceEntry status : " + status);
	}

	@Override
	public void setlogOutTime(String logOutTime, String empid, int rowNum)
			throws Exception {
		PreparedStatement preparedStatement = conn
				.prepareStatement(AttendanceTableSQLConstants.setLogOutTime);
		preparedStatement.setString(1, logOutTime);
		preparedStatement.setString(2, empid);
		preparedStatement.setInt(3, rowNum);

		PayRollUtility.debug(preparedStatement.toString());
		int status = preparedStatement.executeUpdate();
		PayRollUtility.debug("addAttandeanceEntry status : " + status);
	}

	public void modifyAttandance(HashMap<Integer, Attendance> attandanceMap)
			throws Exception {
		PreparedStatement preparedStatement = conn
				.prepareStatement(AttendanceTableSQLConstants.modifyAttandance);
		Iterator<Integer> mapIterator = attandanceMap.keySet().iterator();
		while (mapIterator.hasNext()) {
			Attendance cur = attandanceMap.get(mapIterator.next());
			preparedStatement.setString(1, cur.getLoginTime());
			preparedStatement.setString(2, cur.getLogoutTime());
			preparedStatement.setString(3, cur.getComments());
			preparedStatement.setString(4, cur.getModifiedBy());
			preparedStatement.setInt(5, cur.getRownumber());
			preparedStatement.setString(6, cur.getEmployee().getEmpId());
			System.out.println("preparedStatement : " + preparedStatement);
			preparedStatement.executeUpdate();
		}
	}

	@Override
	public ArrayList<Attendance> getAttandaceInfo(String empId, String date)
			throws Exception {

		ArrayList<Attendance> attandanceList = new ArrayList<Attendance>();
		Attendance attandance = null;
		PreparedStatement preparedStatement = null;
		
		
		if(date == null){
			
			preparedStatement =  conn.prepareStatement(AttendanceTableSQLConstants.retriveFromAttandance+AttendanceTableSQLConstants.whenDateNull);
			
			preparedStatement.setString(1, empId);
			preparedStatement.setString(2, empId);
			preparedStatement.setBoolean(3, true);
		}else{
			
			preparedStatement = conn.prepareStatement(AttendanceTableSQLConstants.retriveFromAttandance+AttendanceTableSQLConstants.whenDateNotNull);
			preparedStatement.setString(1, empId);
			preparedStatement.setString(2, date);
		}

		PayRollUtility.debug(preparedStatement.toString());
		ResultSet rs = preparedStatement.executeQuery();

		if (!rs.isBeforeFirst()) {
			PayRollUtility.debug(ExceptionMsg.NO_ATT_INFO_FOUND_FOR_ID + empId);
			return null;
		}
		while (rs.next()) {
			attandance = new Attendance();
			attandance.setRownumber(rs
					.getInt(AttendanceTableSQLConstants.rowNumber));

			Employee employee = new Employee();
			employee.setEmpId(rs
					.getString(AttendanceTableSQLConstants.employeeId));
			attandance.setEmployee(employee);

			attandance.setLoginTime(rs.getString(AttendanceTableSQLConstants.logIn));
			attandance.setLogoutTime(rs.getString(AttendanceTableSQLConstants.logOut));
			
			
			attandance.setDate(rs.getString(AttendanceTableSQLConstants.date));
			attandance.setComments(rs
					.getString(AttendanceTableSQLConstants.comments));
			attandance.setModifiedBy(rs
					.getString(AttendanceTableSQLConstants.modifiedBy));
			attandance.setCreatedBy(rs
					.getString(AttendanceTableSQLConstants.createBy));
			attandance.setFirstIn(rs
					.getBoolean(AttendanceTableSQLConstants.firstLogin));
			attandance.setLastOut(rs.getBoolean(AttendanceTableSQLConstants.lastLogout));

			attandanceList.add(attandance);
		}
		PayRollUtility.debug(attandanceList.toString());
		return attandanceList;
	}

	/**
	 * The main method.
	 * 
	 * @param args
	 *            the arguments
	 * @throws Exception
	 *             the exception
	 */
	@Override
	public void updateLogOutStatus(String empId, String date, boolean status) throws Exception {
		System.out.println("Update logout ; "+AttendanceTableSQLConstants.updateLogOutStatus);
		
		int maxRowNum = getMaxRowForEmployee(empId,date);
		System.out.println("maxRowNum : "+maxRowNum);
		if(maxRowNum< 0)
			return;
		
		PreparedStatement preparedStatement;
			preparedStatement = conn.prepareStatement(AttendanceTableSQLConstants.updateLogOutStatus);
			preparedStatement.setBoolean(1, status);
			preparedStatement.setString(2, empId);
			preparedStatement.setInt(3, maxRowNum);
			
			PayRollUtility.debug("preparedStatement updateLogOutStatus ; " +preparedStatement );
			preparedStatement.executeUpdate();
			
		
	}

	private int getMaxRowForEmployee(String empId, String date) throws Exception{
		System.out.println("Update logout ; "+AttendanceTableSQLConstants.getMaxRowForUpdate);
		
		String query = AttendanceTableSQLConstants.getMaxRowForUpdate;
		
		if(date != null){
			
			query =  query + AttendanceTableSQLConstants.getRowForGivenDate;
		}
		PreparedStatement preparedStatement = conn.prepareStatement(query);
		preparedStatement.setString(1, empId);
		if(date != null)
			preparedStatement.setString(2, date);

		ResultSet rs = preparedStatement.executeQuery();
		
		
		if(rs.isBeforeFirst() && rs.next()){
			return rs.getInt(1);
		}
		return -1;
	}
	
	public ArrayList<HashMap<String,String>> employeeTimesheetInfo(String empId,String companyCode,
			String department,String from_date,String to_date,String unit, int mode)
			throws BusinessException {
		
		String query = (mode == 0) ? AttendanceTableSQLConstants.getEmployeeTimeSheetInfo :
			((mode == 1) ? AttendanceTableSQLConstants.getEmployeeDailyConsolidatedTimesheetInfo : 
			AttendanceTableSQLConstants.getEmployeeMonthlyConsolidatedTimesheetInfo);
		if(empId != null){
			query = query + " and eid ='" + empId+ "'";
		}
		if(companyCode != null){
			query = query + " and company ='" + companyCode+ "'";
		}
		if(department != null)
		{
			query = query + " and department ='" + department + "'";
		}
		if(unit != null)
		{
			query = query + " and unit ='" + unit + "'";
		}
		query = query+"order by eid, attDate";
		PayRollUtility.debug("employeeTimesheetInfo query : "+query);
		try{
		PreparedStatement preparedStatement = conn.prepareStatement(query);
		preparedStatement.setString(1, from_date);
		preparedStatement.setString(2, to_date);
		preparedStatement.setString(3, from_date);
		preparedStatement.setString(4, to_date);
		System.out.println("preparedStatement : "+preparedStatement);
		ResultSet rs = preparedStatement.executeQuery();
		if(!rs.isBeforeFirst()){
			return null;
		}
		ArrayList<HashMap<String,String>> details = new ArrayList<HashMap<String,String>>();
		while(rs.next()){
			  HashMap<String, String> map = new HashMap<String, String>();
			   map.put(CustomTableConstants.EMPI, rs.getString(CustomTableConstants.EMPI));
			   map.put(CustomTableConstants.name,rs.getString(CustomTableConstants.name));
			   map.put(CustomTableConstants.attDate,rs.getString(CustomTableConstants.attDate));
			   map.put(CustomTableConstants.from_time,rs.getString(CustomTableConstants.from_time));
			   map.put(CustomTableConstants.to_time,rs.getString(CustomTableConstants.to_time));
			   map.put(CustomTableConstants.total,rs.getString(CustomTableConstants.total));
			   map.put(CustomTableConstants.company,rs.getString(CustomTableConstants.company));
			   map.put(CustomTableConstants.department,rs.getString(CustomTableConstants.department));
			   map.put(CustomTableConstants.reason,rs.getString(CustomTableConstants.reason));
			   map.put(CustomTableConstants.rowNum, String.valueOf(rs.getInt(CustomTableConstants.rowNum)));
			   map.put(CustomTableConstants.firstLogin, String.valueOf(rs.getBoolean(CustomTableConstants.firstLogin)));
			   map.put(CustomTableConstants.lastLogOut, String.valueOf(rs.getBoolean(CustomTableConstants.lastLogOut)));
			   map.put(CustomTableConstants.weeklyOff, String.valueOf(rs.getString(CustomTableConstants.weeklyOff)));
			   map.put(CustomTableConstants.hoursperday, String.valueOf(rs.getString(CustomTableConstants.hoursperday)));
			   details.add(map);
			   
			  }
		
		return details;
		}catch (Exception e){ 
			e.printStackTrace();	
			throw new BusinessException(ERROR_WHILE_RETRIVING_ATTANDANCE_INFO);
		}
	}

	@Override
	public void deleteAttEntry(String empId, int rowNum, String date)throws BusinessException {

		try {
			PreparedStatement preparedStatement = conn.prepareStatement(AttendanceTableSQLConstants.deleteAttEtry);
			preparedStatement.setString(1, empId);
			preparedStatement.setInt(2, rowNum);
			preparedStatement.setString(3, date);
			preparedStatement.executeUpdate();
			
		} catch (Exception e) {
			throw new BusinessException(ERROR_WHILE_DELETING_ATTANDANCE_INFO);
		}
	}
	
	public void  modifyAttendance(String empId,String date,String intime, boolean First,String outTime, boolean last,int rowno)throws BusinessException
	 {
	  try {
	   PreparedStatement preparedStatement = conn.prepareStatement(AttendanceTableSQLConstants.modifyAttandance);
	   preparedStatement.setString(1, intime);
	   preparedStatement.setBoolean(2,First);
	   preparedStatement.setString(3,outTime);
	   preparedStatement.setBoolean(4,last);
	   preparedStatement.setString(5,empId);
	   preparedStatement.setString(6,date);
	   preparedStatement.setInt(7,rowno);
	   PayRollUtility.debug("preparedStatement modifyAttendance : "+preparedStatement);
	   
	   preparedStatement.executeUpdate();
	     
	  } catch (SQLException e){
		  e.printStackTrace();
	   throw new BusinessException(ERROR_WHILE_MODIFYING_ATTANDANCE_INFO);
	  }
	 }

	@Override
	public void updateRowNumber(int rowNum,String empId, String loginTime,
			String logOutTime, String date) throws Exception {
		
		 try {
			 
				PreparedStatement preparedStatement = conn.prepareStatement(AttendanceTableSQLConstants.updaeRowNUm);
				preparedStatement.setInt(1, rowNum);
				preparedStatement.setString(2, empId);
				preparedStatement.setString(3, loginTime);
				preparedStatement.setString(4, logOutTime);
				preparedStatement.setString(5, date);
				
				PayRollUtility.debug("updateRowNumber  preparedStatement" +preparedStatement);
				preparedStatement.executeUpdate();
			} catch (Exception e) {
				e.printStackTrace();
			}
	}
	
	public   LinkedHashMap<String, ArrayList<Attendance>> getAttEntry(String date,String company,String unit, String dept) throws Exception{
		
		LinkedHashMap<String, ArrayList<Attendance>> attInfoList=  new LinkedHashMap<String, ArrayList<Attendance>>();
		 try {
			 //AND emp.unitCode = ? AND emp.DeptCode = ?";
			 	String query = AttendanceTableSQLConstants.getAttENtry;
				
				
				if(unit != null){
					query =query+" AND emp.unitCode = ? ";
				}
				if(dept != null){
					query =query+" AND emp.DeptCode = ? ";
				}

				query =query+" ORDER BY emp.EmployeeId";
				PreparedStatement preparedStatement = conn.prepareStatement(query);
				preparedStatement.setString(1, date);
				preparedStatement.setString(2, company);
				preparedStatement.setString(3, date);
				preparedStatement.setString(4, date);
				preparedStatement.setString(5, date);
				preparedStatement.setString(6, date);
				preparedStatement.setString(7, date);
				preparedStatement.setString(8, date);
				
				if(unit != null){
					preparedStatement.setString(9, unit);
				}
				if(unit == null && dept != null){
					
					preparedStatement.setString(9, dept);
				}else if (unit != null && dept != null){
					
					preparedStatement.setString(10, dept);
				}
				
				PayRollUtility.debug("getAttEntry  preparedStatement" +preparedStatement);
				ResultSet rs =  preparedStatement.executeQuery();
				while(rs.next()){
					String empIs = rs.getString("empid");
					String name = rs.getString("name");
					String inTime = rs.getString("LogIn");
					String outTime = rs.getString("LogOut");
					int rowNum = rs.getInt("RowNum");
					
					Attendance att = new Attendance();
					Employee emp = new Employee();
					emp.setEmpId(empIs);
					emp.setEmpName(name);
					att.setEmployee(emp);

					if(inTime != null && outTime !=null){
						
						att.setLoginTime(inTime);
						att.setLogoutTime(outTime);
						att.setRownumber(rowNum);
					}
					if(attInfoList.get(empIs) == null){
						ArrayList<Attendance> attList = new ArrayList<Attendance>();
						attInfoList.put(empIs, attList);
					}
					attInfoList.get(empIs).add(att);						
				}
				
			} catch (Exception e) {
				e.printStackTrace();
				throw new Exception("Error while getting attendance info.");
			
			}
		 return attInfoList;
	}
}
